﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.SECURITY.MODEL;

namespace KPIS.GERP.SECURITY.DAL
{
    public class HrmEmployeeDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public HrmEmployeeDAL()
        {

        }
        
        public DataSet GetEmployeeInfo(Nullable<int> Sequence, Nullable<int> EmployeeType, Nullable<int> WorkingJobSite, string SearchText)
        {
            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@SearchText", SearchText);
                dbManager.AddParameters(3, "@EmployeeType", EmployeeType);
                dbManager.AddParameters(4, "@WorkingJobSite", "%/" + WorkingJobSite.ToString() + "/%");

                strSQL = "SELECT e.EMP_SEQ, e.NAME_PREFIX_SEQ, np.PREFIX_NAME, e.FIRST_NAME, e.LAST_NAME"
                        + ", e.FIRST_NAME_ENG, e.LAST_NAME_ENG"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS FULL_NAME"
                        + ", CONCAT(ifnull(np.PREFIX_NAME_ENG, ''), e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) AS FULL_NAME_ENG"
                        + ", e.GENDER_SEQ, e.RELIGION_SEQ, e.RACE_NAT_SEQ, e.NAT_SEQ, e.BIRTH_DATE, e.EMP_TYPE_SEQ"
                        + ", e.CLASS_SEQ, e.SALARY_LEVEL_SEQ, e.SALARY, e.POS_SEQ, e.WORKING_JOB_SITE_SEQ"
                        + ", e.DATE_ADMITTED, e.DATE_WORK_START, e.DATE_QUIT, e.EMP_STATUS_SEQ, e.EMP_SEC_SEQ"
                        + ", e.CITIZEN, e.TAX_PAYER , e.SOCIAL, e.OFFICIAL, e.BLOOD_GROUP_SEQ, e.PROBATION_DATE_END, e.DEATH_DATE"
                        + ", e.DATE_LATEST_POS, e.DATE_LATEST_CLASS, e.DATE_LATEST_WORKING_JOB_SITE, e.FUND_MEMBER"
                        + ", p.POS_REF, p.POS_NAME, p.JOB_SITE_SEQ, pj.JOB_SITE_NAME"
                        + ", c.CLASS_NAME, wj.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME, et.EMP_TYPE_NAME"
                        + ", gen.GENDER_NAME, re.RELIGION_NAME, rnat.NAT_NAME AS RACE_NAT_NAME, nat.NAT_NAME"
                        + ", es.EMP_STATUS_NAME, f.POS_TYPE_SEQ, b.BLOOD_GROUP_NAME, ms.MAR_STAT_NAME"
                        + ", f.FIELD_POS_NAME, ex.EXEC_POS_NAME"
                        + ", CONCAT(p.POS_REF, ' - ', p.POS_NAME) AS POS_TEXT"
                   + " FROM HRM_PS_EMPLOYEES e"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
                        + " LEFT JOIN MAS_GENDERS gen ON e.GENDER_SEQ = gen.GENDER_SEQ"
                        + " LEFT JOIN MAS_RELIGIONS re ON e.RELIGION_SEQ = re.RELIGION_SEQ"
                        + " LEFT JOIN MAS_NATIONALITIES rnat ON e.RACE_NAT_SEQ = rnat.NAT_SEQ"
                        + " LEFT JOIN MAS_NATIONALITIES nat ON e.NAT_SEQ = nat.NAT_SEQ"
                        + " LEFT JOIN MAS_BLOOD_GROUPS b ON e.BLOOD_GROUP_SEQ = b.BLOOD_GROUP_SEQ"
                        + " LEFT JOIN MAS_MARITAL_STATUSES ms ON e.MAR_STAT_SEQ = ms.MAR_STAT_SEQ"
                   + " WHERE e.RECORD_STATUS = @ActiveRecord"
                   + " AND es.ACTIVE not like 0"
                   + (Sequence.HasValue ? " AND e.EMP_SEQ = @Sequence" : "")
                   + (EmployeeType.HasValue ? " AND e.EMP_TYPE_SEQ = @EmployeeType" : "")
                   + (WorkingJobSite.HasValue ? " AND wj.JOB_SITE_FULL_PATH LIKE @WorkingJobSite" : "")
                   + " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                        + " ELSE (e.FIRST_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR e.LAST_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR c.CLASS_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR p.POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR pj.JOB_SITE_NAME LIKE CONCAT('%', @SearchText , '%')"
                                + ") END"
                   + " AND e.EMP_SEQ NOT IN(select emp_seq from sys_sc_users where record_status = @ActiveRecord)"
                   + " ORDER BY p.POS_REF + 1";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }
    }
}
